Generated code - Calling a stored procedure, SelfServicing
Preface
LLBLGen Pro supports stored procedures by offering the ability to define
calls to those stored procedures. There are two types of stored
procedures: procedures which do not return a resultset, called
Action
Stored Procedures, and procedures which return one or more resultsets,
which are called
Retrieval Stored Procedures. This section
illustrates how call definitions to these stored procedures in your project
are generated in code and how you can use them in your code.
To fetch a
TypedView mapped onto a resultset of a
stored procedure please see
using the TypedView classes.
Retrieval Stored Procedure Calls
When you add a call definition for a retrieval stored procedure to the
project in the LLBLGen Pro designer, a static/shared method that will call
that stored procedure will be added to a class called
RetrievalProcedures.
If the stored procedure returns a single resultset, the return value of the
generated method will be a
DataTable. When the stored procedure
returns more than one resultset, the return value of the generated method
will be a
DataSet, containing each resultset in a separate
DataTable.
For example, if we add a call definition to our
LLBLGen Pro project to the procedure in Northwind called
CustOrderDetail,
taking one parameter, an OrderID, a static method called
CustOrderDetail
is created, returning a
DataTable (because the procedure returns
a single resultset) and accepting a single parameter, orderID, which is of
type int/Integer because the parameter itself is of type integer. To utilize
this method in your own code, you can call as shown below. For the orderID,
the value 10254 is passed as the parameter value:
// [C#]
DataTable resultSet = RetrievalProcedures.CustOrderDetail(10254);
' [VB.NET]
Dim resultSet As DataTable = RetrievalProcedures.CustOrderDetail(10254)
Output parameters are also supported. When a stored procedure has an output parameter, a parameter representing the output
parameter in the stored procedure is added to the method heading and is defined as 'ref' (C#) or 'ByRef' (VB.NET). Illustrated
below is the call to an imaginary stored procedure which returns a datatable, takes 4 input parameters
and returns a value in an output parameter:
// [C#]
int outputValue;
DataTable resultSet = RetrievalProcedures.MyStoredProcedure(1, 2, 3, 4, ref outputValue);
' [VB.NET]
Dim outputValue as Integer
Dim resultSet As DataTable = RetrievalProcedures.MyStoredProcedure(1, 2, 3, 4, outputValue)
Action Stored Procedure Calls
If you have added a call to a procedure to your project which does not return a resultset, the static/shared method is
added to the class
ActionProcedures. Instead of returning a DataTable or DataSet, a method in this class returns an int/Integer,
which represents the return value of the ExecuteNonQuery() method, which is the number of rows affected if the database
has row counting enabled (and the stored procedure doesn't switch it off). For the rest, the action stored procedure methods
work the same as the retrieval stored procedures mentioned above: input parameters are defined as normal parameters for the
method and output parameters are defined as ref/ByRef parameters.
Transaction Support
Both Action and Retrieval Procedures can be called within an active transaction. By using the overload which accepts an ITransaction
object, you can pass in an existing ITransaction instance which contains a valid ADO.NET connection object
which is used when calling the stored procedure. This will make the stored procedure to run inside the transaction controlled by the ITransaction object.
Note:
|
If you pass in a Transaction object, which controls an ADO.NET
transaction, keep in mind that that transaction is already a valid
database transaction, so when you rollback a transaction inside your
stored procedure, you will rollback the entire transaction with that
statement.
|
Wrap call in IRetrievalQuery object
LLBLGen Pro offers you to get the call to a retrieval stored procedure as an
IRetrievalQuery object. An
IRetrievalQuery object is the query
object generated by a Dynamic Query Engine (DQE) and which is executed by
the low level fetch logic of LLBLGen Pro's O/R mapper core. The
IRetrievalQuery object allows you to fetch a query as a datareader or to
project the results of the stored procedure call onto a data-structure of
your choice, for example an entity collection. You retrieve an
IRetrievalQuery object which wraps the call to a given stored procedure
by calling the following generated method (each retrieval stored procedure
has such a method generated):
// C#
IRetrievalQuery procCall = RetrievalProcedures.GetStoredProcedureCallNameCallAsQuery(parameters);
' VB.NET
Dim procCall As IRetrievalQuery = RetrievalProcedures.GetStoredProcedureCallNameCallAsQuery(parameters)
You can then pass the IRetrievalQuery object to the methods for fetching a datareader or fetch a projection. See for more information about fetching
a datareader or fetching a projection:
LLBLGen Pro - Fetching DataReaders and projections.